package com.newedu.equipmgr.dao;

import com.newedu.equipmgr.entity.AreaInfo;
import com.newedu.equipmgr.util.MyDBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author jerry
 * @create 2020-06-08 09:00:29
 * @company 新开普电子股份有限公司
 */
public class AreaDaoImpl implements AreaDao {
    //dbutils jdbc helper 来进行jdbc操作
    // 传递一个数据库的连接池对象，然后构造一个QueryRunner 查询执行器
    QueryRunner queryRunner = new QueryRunner(MyDBUtils.getDataSource());

    @Override
    public boolean insert(AreaInfo areaInfo) throws SQLException {

        String sql = "insert into t_areas (building,floor) values (?,?)";
        int update = queryRunner.update(sql, areaInfo.getBuilding(), areaInfo.getFloor());
        return update==1?true:false;
    }

    @Override
    public boolean update(AreaInfo areaInfo) throws SQLException {
        String sql = "update t_areas set building=? , floor=? where id=?";
        Object[] params = new Object[]{areaInfo.getBuilding(), areaInfo.getFloor(),areaInfo.getId()};
        int update = queryRunner.update(sql, params);
        return update==1?true:false;
    }

    @Override
    public boolean delete(int id) throws SQLException {
        String sql = "delete from t_areas where id=?";
        int update = queryRunner.update(sql, id);
        return update==1?true:false;
    }

    @Override
    public AreaInfo selectOne(int id) throws SQLException {
        String sql = "select id, building,floor from t_areas where id=?";
        AreaInfo areaInfo = queryRunner.query(sql, new BeanHandler<>(AreaInfo.class), id);
        return areaInfo;
    }

    @Override
    public List<AreaInfo> selectAll() throws SQLException {
        String sql = "select id, building,floor from t_areas";
        List<AreaInfo> areaInfoList = queryRunner.query(sql, new BeanListHandler<>(AreaInfo.class));
        return areaInfoList;
    }

    @Override
    public List<AreaInfo> selectBy(String condition, int pageNum, int pageSize) throws SQLException {
        String sql = "select id, building,floor from t_areas where  1=1";
        if(condition!=null && !condition.isEmpty()){
            sql += condition;
        }
        if(pageSize >0){
            sql += " limit " + pageSize*(pageNum-1) +" , "+pageSize;
        }
        List<AreaInfo> areaInfoList = queryRunner.query(sql, new BeanListHandler<>(AreaInfo.class));
        return areaInfoList;
    }

    @Override
    public int countRows(String condition) throws SQLException {
        String sql = "select count(*) from t_areas where 1=1";
        if(condition!=null && !condition.isEmpty()){
            sql += condition;
        }
        Number query = queryRunner.query(sql, new ScalarHandler<>());
        return query.intValue();
    }

    @Override
    public List<String> selectAllBuilding() throws SQLException {
        String sql = "SELECT DISTINCT building FROM t_areas";
        List<String> list = queryRunner.query(sql, new ColumnListHandler<String>("building"));
        return list;
    }

    @Override
    public List<AreaInfo> selectAreaInfoBy(String building) throws SQLException {
        String sql = "select * from t_areas where 1=1 ";
        // 对参数进行合法性校验
        if(building != null && !building.isEmpty()){
            sql += building;
        }
        System.out.println(sql);
        List<AreaInfo> areaInfoList = queryRunner.query(sql, new BeanListHandler<AreaInfo>(AreaInfo.class));
        return areaInfoList;
    }
}
